package DAO.mysql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import model.core.graph.IRelationship;
import model.core.graph.IToken;
import model.core.graph.RelationType;
import model.core.graph.TypeRelation;
import model.core.graph.WordToken;
import DAO.IWordDAO;

public class MySQLWordDAO implements IWordDAO {
	private MySQLConnectionPool pool;
	
	public MySQLWordDAO(MySQLConnectionPool pool) {
		this.pool = pool;
	}

	@Override
	public boolean declareToken(IToken tken) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = pool.getConnection();
			stmt = conn.prepareStatement("INSERT ignore INTO wordnode(word) values(?)");
			stmt.setString(1, tken.getWord());
			stmt.execute();
			IToken ret = this.getToken(tken.getWord());
			tken.setID(ret.getID());
			return false;
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				pool.returnConnection(conn);
			}
		}
		return false;
	}

	@Override
	public boolean addRelation(IToken from, IToken to,
			IRelationship relationShip) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		CallableStatement call = null;
		try {
			conn = pool.getConnection();
			stmt = conn.prepareStatement("INSERT ignore INTO relation(type, src, dest) values(?, ?, ?)");
			stmt.setInt(1, relationShip.getType().ordinal());
			stmt.setInt(2, from.getID());
			stmt.setInt(3, to.getID());
			stmt.executeUpdate();
			IRelationship relation = this.getRelation(from, to, relationShip.getType());
			relationShip.setID(relation.getID());
			call = conn.prepareCall("{call addRelationCount(?)}");
			call.setInt(1, relationShip.getID());
			call.execute();
			return false;
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(call != null) {
				try {
					call.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				pool.returnConnection(conn);
			}
		}
		return false;
	}

	@Override
	public IToken getToken(int id) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = pool.getConnection();
			stmt = conn.prepareStatement("SELECT * FROM wordnode where nodeID=?");
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if(rs.next()) {
				IToken tken = new WordToken(rs.getString(2));
				tken.setID(rs.getInt(1));
				return tken;
			}
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				pool.returnConnection(conn);
			}
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	@Override
	public IToken getToken(String name) {
		if(name == null) return null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = pool.getConnection();
			stmt = conn.prepareStatement("SELECT * FROM wordnode where word=?");
			stmt.setString(1, name);
			rs = stmt.executeQuery();
			if(rs.next()) {
				IToken tken = new WordToken(rs.getString(2));
				tken.setID(rs.getInt(1));
				return tken;
			}
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				pool.returnConnection(conn);
			}
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	@Override
	public IRelationship getRelation(IToken from, IToken to, RelationType type) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = pool.getConnection();
			stmt = conn.prepareStatement("SELECT * FROM relation where src=? and dest = ? and type=?");
			stmt.setInt(1, from.getID());
			stmt.setInt(2, to.getID());
			stmt.setInt(3, type.ordinal());
			rs = stmt.executeQuery();
			if(rs.next()) {
				IRelationship rlt = new TypeRelation(from, to, RelationType.values()[rs.getInt(2)]);
				rlt.setID(rs.getInt(1));
				return rlt;
			}
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				pool.returnConnection(conn);
			}
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	@Override
	public List<IRelationship> getRelationshipOut(IToken from, RelationType type, float bias) {
		Connection conn = null;
		PreparedStatement stmt = null, stmt2 = null;
		ResultSet rs = null,rs2 = null;
		
		try {
			conn = pool.getConnection();
			stmt = conn.prepareStatement("select sum(count) from relation where src=? and type=? and src!= dest group by src");
			stmt.setInt(1, from.getID());
			stmt.setInt(2, type.ordinal());
			rs = stmt.executeQuery();
			int total = 0;
			if(rs.next()) {
				total = rs.getInt(1);
			}
			int b = (int) (bias * total);
			stmt2 = conn.prepareStatement("select * from relation where src=? and type=? and src!=dest and count > ?");
			stmt2.setInt(1, from.getID());
			stmt2.setInt(2, type.ordinal());
			stmt2.setInt(3, b);
			rs2 = stmt2.executeQuery();
			List<IRelationship> relations = new LinkedList<IRelationship>();
			while(rs2.next()) {
				IToken src = this.getToken(rs2.getInt(3));
				IToken to = this.getToken(rs2.getInt(4));
				IRelationship relation = new TypeRelation(src, to, RelationType.values()[rs2.getInt(2)]);
				relations.add(relation);
			}
			return relations;
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(rs2 != null) {
				try {
					rs2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt2 != null) {
				try {
					stmt2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				pool.returnConnection(conn);
			}
		}
		return null;
	}

	@Override
	public List<IRelationship> getRelationshipIn(IToken in, RelationType type, float bias) {
		Connection conn = null;
		PreparedStatement stmt = null, stmt2 = null;
		ResultSet rs = null,rs2 = null;
		
		try {
			conn = pool.getConnection();
			stmt = conn.prepareStatement("select sum(count) from relation where dest=? and type=? and dest!= src group by dest");
			stmt.setInt(1, in.getID());
			stmt.setInt(2, type.ordinal());
			rs = stmt.executeQuery();
			int total = 0;
			if(rs.next()) {
				total = rs.getInt(1);
			}
			int b = (int) (bias * total);
			stmt2 = conn.prepareStatement("select * from relation where dest=? and type=? and dest!=src and count > ?");
			stmt2.setInt(1, in.getID());
			stmt2.setInt(2, type.ordinal());
			stmt2.setInt(3, b);
			rs2 = stmt2.executeQuery();
			List<IRelationship> relations = new LinkedList<IRelationship>();
			while(rs2.next()) {
				IToken src = this.getToken(rs2.getInt(3));
				IToken to = this.getToken(rs2.getInt(4));
				IRelationship relation = new TypeRelation(src, to, RelationType.values()[rs2.getInt(2)]);
				relations.add(relation);
			}
			return relations;
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(rs2 != null) {
				try {
					rs2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt2 != null) {
				try {
					stmt2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				pool.returnConnection(conn);
			}
		}
		return null;
	}

	@Override
	public IToken[] getTokens(IRelationship relation) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = pool.getConnection();
			stmt = conn.prepareStatement("SELECT * FROM relation where relationID=?");
			stmt.setInt(1, relation.getID());
			rs = stmt.executeQuery();
			IToken[] result = new IToken[2];
			if(rs.next()) {
				IToken src = this.getToken(rs.getInt(3));
				IToken dest = this.getToken(rs.getInt(4));
				result[0] = src;
				result[1] = dest;
			}
			return result;
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				pool.returnConnection(conn);
			}
		}
		return null;
	}

	@Override
	public IRelationship getRelation(int relationID) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = pool.getConnection();
			stmt = conn.prepareStatement("SELECT * FROM relation where relationID=?");
			stmt.setInt(1, relationID);
			rs = stmt.executeQuery();
			if(rs.next()) {
				IToken from = this.getToken(rs.getInt(3));
				IToken to = this.getToken(rs.getInt(4));
				IRelationship relation = new TypeRelation(from, to, RelationType.values()[rs.getInt(2)]);
				relation.setID(relationID);
				return relation;
			}
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				pool.returnConnection(conn);
			}
		}
		return null;
	}

	@Override
	public List<IRelationship> getRelationshipOut(IToken from,
			RelationType type, int limit) {
		if(from == null) return null;
		Connection conn = null;
		PreparedStatement stmt2 = null;
		ResultSet rs2 = null;
		
		try {
			conn = pool.getConnection();
			stmt2 = conn.prepareStatement("select * from relation where src=? and type=? and src!= dest ORDER BY count desc LIMIT ?");
			stmt2.setInt(1, from.getID());
			stmt2.setInt(2, type.ordinal());
			stmt2.setInt(3, limit);
			rs2 = stmt2.executeQuery();
			List<IRelationship> relations = new LinkedList<IRelationship>();
			while(rs2.next()) {
				IToken src = this.getToken(rs2.getInt(3));
				IToken to = this.getToken(rs2.getInt(4));
				IRelationship relation = new TypeRelation(src, to, RelationType.values()[rs2.getInt(2)]);
				relations.add(relation);
			}
			return relations;
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(rs2 != null) {
				try {
					rs2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt2 != null) {
				try {
					stmt2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				pool.returnConnection(conn);
			}
		}
		return null;
	}

	@Override
	public List<IRelationship> getRelationshipIn(IToken in, RelationType type,
			int limit) {
		Connection conn = null;
		PreparedStatement stmt2 = null;
		ResultSet rs2 = null;
		
		try {
			conn = pool.getConnection();
			stmt2 = conn.prepareStatement("select * from relation where dest=? and type=? and dest!=src ORDER BY count desc LIMIT ?");
			stmt2.setInt(1, in.getID());
			stmt2.setInt(2, type.ordinal());
			stmt2.setInt(3, limit);
			rs2 = stmt2.executeQuery();
			List<IRelationship> relations = new LinkedList<IRelationship>();
			while(rs2.next()) {
				IToken src = this.getToken(rs2.getInt(3));
				IToken to = this.getToken(rs2.getInt(4));
				IRelationship relation = new TypeRelation(src, to, RelationType.values()[rs2.getInt(2)]);
				relations.add(relation);
			}
			return relations;
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(rs2 != null) {
				try {
					rs2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt2 != null) {
				try {
					stmt2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				pool.returnConnection(conn);
			}
		}
		return null;
	}

	@Override
	public float getRatio(IToken[] tokens, RelationType type, int portionIdx) {
		for(int i = 0; i < tokens.length; ++i) {
			if(tokens[i] == null) {
				return 0;
			}
		}
		if(tokens[0].getID() == tokens[1].getID()) {
			return 0;
		}
		Connection conn = null;
		PreparedStatement stmt = null, stmt2 = null;
		ResultSet rs = null, rs2 = null;
		
		try {
			conn = pool.getConnection();
			stmt2 = conn.prepareStatement("select count from relation where src=? and dest=? and type=?");
			stmt2.setInt(1, tokens[0].getID());
			stmt2.setInt(2, tokens[1].getID());
			stmt2.setInt(3, type.ordinal());
			rs2 = stmt2.executeQuery();
			int count = 0;
			if(rs2.next()) {
				count = rs2.getInt(1);
			}
			
			if(portionIdx == 0) {
				stmt = conn.prepareStatement("SELECT SUM(count) from relation where src=? and type=? and src!=dest");
			} else {
				stmt = conn.prepareStatement("SELECT SUM(count) from relation where dest=? and type=? and src!=dest");
			}
			stmt.setInt(1, tokens[portionIdx].getID());
			stmt.setInt(2, type.ordinal());
			rs = stmt.executeQuery();
			if(rs.next() && count > 0) {
				int sum = rs.getInt(1);
				if(sum > 0) {
					return count / (float)sum;
				}
			}
		} catch(Throwable e) {
			e.printStackTrace();
		} finally {
			if(rs2 != null) {
				try {
					rs2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(stmt2 != null) {
				try {
					stmt2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				pool.returnConnection(conn);
			}
		}
		return 0;
	}

}
